This page describes some of the changes in the behavior of Oracle Database 10g from that of previous releases. In some cases the default values of some parameters have changed. In other cases new behaviors/requirements have been introduced that may affect current scripts or applications. More detailed information is in the documentation.
The Cost Based Optimizer (CBO) is now enabled by default. Rule-based optimization is not supported in 10g (setting OPTIMIZER_MODE to RULE or CHOOSE is not supported). See Chapter 12, "Introduction to the Optimizer," in Oracle Database Performance Tuning Guide.
Collection of optimizer statistics is now performed by default, automatically for all schemas (including SYS), for pre-existing databases upgraded to 10g, and for newly created 10g databases. Gathering optimizer statistics on stale objects is scheduled by default to occur daily during the maintenance window. See Chapter 15, "Managing Optimizer Statistics" in Oracle Database Performance Tuning Guide.
See the Oracle Database Upgrade Guide for changes in behavior for the COMPUTE STATISTICS clause of CREATE INDEX, and for behavior changes in SKIP_UNUSABLE_INDEXES.
After upgrading to Oracle Database 10g, the minimum supported release to downgrade to is Oracle9i Release 2 (9.2.0.3) or later, and the minimum value for the COMPATIBLE setting is 9.2.0. In other words, only users who have set the database to COMPATIBLE=9.2.0 and who have installed the Oracle9i Release 2 (9.2.0.3) or later executable can downgrade their database.
Users upgrading to Oracle Database 10g from previous releases (such as Oracle 8, Oracle8i or Oracle9i Release 1) cannot downgrade to Oracle9i Release 2 unless they first install Oracle9i Release 2. When upgrading to 10g, by default the database will remain at Oracle9i Release 2 file format compatibility, so the on-disk structures that Oracle Database 10g writes are compatible with Oracle9i Release 2 structures; this makes it possible to downgrade to Oracle9i Release 2. After file format compatibility has been explicitly advanced to 10g (using COMPATIBLE=10.x), it is no longer possible to downgrade. For more information, see the Oracle Database Upgrade Guide.
A SYSAUX tablespace is created upon upgrade to 10g. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle features and products that previously required their own tablespaces, it reduces the number of tablespaces required by Oracle that you, as a DBA, must maintain.
Minimum and default log file sizes are larger. Minimum is now 4 MB, default is 50MB, unless you are using Oracle Managed Files (OMF) when it is 100 MB.
Database performance statistics are now collected by the Automatic Workload Repository (AWR) database component, automatically upon upgrade to 10g and also for newly created 10g databases. This data is stored in the SYSAUX tablespace, and is used by the database for automatic generation of performance recommendations. See Chapter 5, "Automatic Performance Statistics" in the Oracle Database Performance Tuning Guide.
If you currently use Statspack for performance data gathering, see section
1. of the Statspack readme (spdoc.txt in the RDBMS ADMIN
directory) for directions on using Statspack in 10g to avoid conflict
with the AWR.
Automatic PGA Memory Management is now enabled by default (unless PGA_AGGREGATE_TARGET is explicitly set to 0 or WORKAREA_SIZE_POLICY is explicitly set to MANUAL). PGA_AGGREGATE_TARGET is defaulted to 20% of the SGA size, unless explicitly set.Oracle recommends tuning the value of PGA_AGGREGATE_TARGET after upgrading. See Chapter 7, "Memory Configuration and Use," in the Oracle Database Performance Tuning Guide.
Previously, the number of SQL cursors cached by PL/SQL was determined by OPEN_CURSORS. In 10g, the number of cursors cached is determined by SESSION_CACHED_CURSORS. See the Oracle Database Reference manual.
SHARED_POOL_SIZE must increase to include the space needed for shared pool overhead.
Dropped objects are now moved to the recycle bin, where the space is only reused when it is needed. This allows 'undropping' a table using the FLASHBACK DROP feature. See Chapter 14 of the Oracle Database Administrators Guide.
In addition to the SYSTEM tablespace, a SYSAUX tablespace is always created at database creation, and upon upgrade to 10g. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle features and products that previously required their own tablespaces, it reduces the number of tablespaces required by Oracle that you, as a DBA, must maintain. See Chapter 2, “Creating a Database,” in the Oracle Database Administrator’s Guide.
In Oracle Database 10g, by default all new databases are created with 10g file format compatibility. This means you can immediately use all the 10g features. After a database is configured to use 10g compatible file formats, it is not possible to downgrade this database to prior releases. Minimum and default log file sizes are larger. The minimum is now 4 MB and the default is 50MB, unless you are using Oracle Managed Files (OMF) when it is 100 MB.